﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Script.Serialization;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using IFSModel;

namespace IFSController
{

    public class game
    {

        public static DataTable getplaylist(long MemID, long mempid = 0, int Status = 0, long matchid = 0)
        {
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string query = @"select a.*,b.matchtime,b.score,b.isFinish,b.liveA,b.liveB  from Members_play a 
                                left join match b on a.matchid = b.id where 1 = 1";
            MemID = 1;

            if (MemID > 0)
                query += " and a.MemID = @MemID ";
            if (Status > 0)
                query += " and a.Status = @Status ";
            if (matchid > 0)
                query += " and a.matchid = @matchid";
            if (mempid > 0)
                query += " and a.mempid = @mempid";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            if (MemID > 0)
                adapter.SelectCommand.Parameters.AddWithValue("@MemID", MemID);
            if (Status > 0)
                adapter.SelectCommand.Parameters.AddWithValue("@Status", Status);
            if (matchid > 0)
                adapter.SelectCommand.Parameters.AddWithValue("@matchid", matchid);
            if (mempid > 0)
                adapter.SelectCommand.Parameters.AddWithValue("@mempid", mempid);

            DataTable dt = new DataTable();

            Conn.Open();

            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                return dt = null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable getplaylist(int Status, DateTime datepara, int isdone, string leagueid = "")
        {
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string username = System.Web.HttpContext.Current.User.Identity.Name.ToString();
            if (string.IsNullOrEmpty(username))
            {
                username = System.Web.HttpContext.Current.Session["curentUser"].ToString();
            }
            DataTable dtmid = member.getprofile(username);
            long MemID = Convert.ToInt64(dtmid.Rows[0]["memid"]);

            // string query = @"select a.*,b.matchtime,b.score,b.isFinish,b.liveA,b.liveB,c.Mpt_Cal_Point,c.Mpt_Cal_Star from Members_play a 
            //                    left join match b on a.matchid = b.id left join Members_point_tran c on a.mempid = c.refid where 1 = 1 ";

            string query = @"select a.*,b.matchtime,b.score,b.isFinish,b.liveA,b.liveB,c.Mpt_Cal_Point,c.Mpt_Cal_Star,l.type,l.descriptionTh from Members_play a 
                             left join match b on a.matchid = b.id left join Members_point_tran c on a.mempid = c.refid 
                             left join league l on b.leaguename = l.name 
                             where 1  = 1 and a.MemID = @MemID and a.isdone = @isdone ";


            //query += " and a.MemID = @MemID ";

            //if (isdone.Equals(0))
            //{
            //    query += " and a.Createdate BETWEEN CONVERT(VARCHAR,'" + datepara.ToString("yyyy-MM-dd") + "',101)";
            //    query += " and CONVERT(VARCHAR,'" + datepara.AddDays(1).ToString("yyyy-MM-dd") + "',101) ";
            //}

             
            //query += " and a.isdone = @isdone";


            if (leagueid != "")
            {
                query += " and a.leagueid = @leagueid";
                query += " and c.typeid  = 3";
            }
            else {
                query += " and c.typeid = 1";
            }

            query += " order by c.Createdate desc";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            //======================================================================

            adapter.SelectCommand.Parameters.AddWithValue("@MemID", MemID);

            //if (isdone.Equals(0))
            //    adapter.SelectCommand.Parameters.AddWithValue("@Createdate", datepara);

            adapter.SelectCommand.Parameters.AddWithValue("@isdone", isdone);


            if (leagueid != "")
                adapter.SelectCommand.Parameters.AddWithValue("@leagueid", leagueid);


            DataTable dt = new DataTable();

            Conn.Open();

            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                return dt = null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable getplaylist(long matchid)
        {
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dtmid = member.getprofile(System.Web.HttpContext.Current.User.Identity.Name.ToString());
            long MemID = Convert.ToInt64(dtmid.Rows[0]["memid"]);

            string query = @"select matchid from Members_play where memid = @memid ";

            if (!matchid.Equals(0))
                query += "and matchid = @matchid";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            adapter.SelectCommand.Parameters.AddWithValue("@memid", MemID);

            if (!matchid.Equals(0))
                adapter.SelectCommand.Parameters.AddWithValue("@matchid", matchid);


            DataTable dt = new DataTable();

            Conn.Open();

            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                return dt = null;
            }
            finally
            {
                Conn.Close();
            }

            if (dt.Rows.Count > 0)
            {
                return dt;
            }

            return dt = null;

        }

        public static DataTable getmatchresult(DateTime datepara, int isfinish)
        {
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string query = @"select a.livea,a.liveb,a.score,a.matchtime,a.isfinish,b.descriptionth,b.name
                            from match a inner join league b on a.leaguename = b.name  
                            where 1  = 1 and a.isfinish = 1";


            query += " and a.matchtime BETWEEN CONVERT(VARCHAR,'" + datepara.AddDays(-1).ToString("yyyy-MM-dd") + "',101)";
            query += " and CONVERT(VARCHAR,'" + datepara.AddDays(1).ToString("yyyy-MM-dd") + "',101) "; 

            query += " and a.isfinish = @isfinish";
            query += " order by b.name,a.matchtime desc";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            //======================================================================

            adapter.SelectCommand.Parameters.AddWithValue("@isfinish", isfinish);  
            //adapter.SelectCommand.Parameters.AddWithValue("@Createdate", datepara);
             
            DataTable dt = new DataTable();

            Conn.Open();

            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                return dt = null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static void UpdateMemberPlay()
        {
            var serializer = new JavaScriptSerializer();
            using (var dataContext = new IFNDBDataContext())
            {
                try
                {
                    var mathcs = from matches in dataContext.matches
                                 join mplay in dataContext.Members_plays on matches.id equals mplay.MatchID
                                 where matches.isFinish == true && mplay.IsDone == false
                                 select new
                                 {
                                     mplay,
                                     Score = matches.score
                                 };
                    var test = mathcs;
                    foreach (var m in mathcs)
                    {
                        int a = 0, b = 0;
                        if (!string.IsNullOrEmpty(m.Score))
                        {
                            string sa = m.Score.Split('-')[0];
                            string sb = m.Score.Split('-')[1];
                            int.TryParse(sa, out a);
                            int.TryParse(sb, out b);
                        }

                        m.mplay.ScoreA = a;
                        m.mplay.ScoreB = b;
                        var mPaly =  dataContext.Members_plays.Single(prop => prop.MempID == m.mplay.MempID);
                        if (mPaly != null)
                        {
                            mPaly.ScoreA = a;
                            mPaly.ScoreB = b;
                            mPaly.IsDone = true;
                            mPaly.Updatedate = DateTime.Now;
                        }
                        decimal point = 0;
                        decimal star = 0;

                        if (m.mplay.calMatchPoint(out point, out star))
                        {
                            dataContext.Members_point_trans.InsertOnSubmit(new Members_point_tran
                            {
                                MemID = m.mplay.MemID,
                                RefID = m.mplay.MempID,
                                TypeID = 3, //do we need to change this to Enum
                                Mpt_Cal_Point = Convert.ToInt32(point),
                                Mpt_Cal_Star = star,
                                Createdate = DateTime.Now
                            });
                        }
                        var mMember = dataContext.Members.Single(prop => prop.MemID == m.mplay.MemID);
                        if (mMember != null)
                        {
                            mMember.Curent_Point += Convert.ToInt32(point);
                            mMember.Curent_Star += (star < 0 ? (point.Equals(-1) ? star * -1 : 0) : star); // if star < 0 check point -1 get half then +half to balance
                            mMember.Updatedate = DateTime.Now;
                        }
                    }
                    dataContext.SubmitChanges();
                    //return mathcs;
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "getMatch function",
                        logType = 1
                    });
                    //return null;
                }
            }
        }

    }




}
